﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{

    public class QCORDERData : BaseFunction
    {

        public QCORDERData()
            : base()
        {

        }
        public QCORDERData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }
        public DataSet GetRawData(DataRow dwABPOS)
        {
            DataSet set4Return = new DataSet();
            DataSet setLocal = new DataSet();
            DataSet setResult = new DataSet();
            DataSet setTemp = new DataSet();
            DataTable tableLocal = null;
            string ARTICLE, LOT, FKRS, FLFD, FPOS, FROMDATE, ENDDATE;
            ARTICLE = dwABPOS["ARTICLE"].ToString().Trim();
            LOT = dwABPOS["LOT"].ToString().Trim();
            FROMDATE = dwABPOS["FROMDATE"].ToString().Trim();
            ENDDATE = dwABPOS["ENDDATE"].ToString().Trim();
            try
            {
                OracleCommand or1 = OracleDB.CreateCommand();

                #region
                or1.CommandText = " select TO_NUMBER(A.QOKORDER) AS QCORDER,A.QOKNEUD,A.QOKERLDAT,A.QOKGEBINDE,A.QOKSTATUS,A.QOKRESULT, "
                    + " A.QOKPNRKRS||'-'||TO_NUMBER(A.QOKPLFDNR) AS QPLAN,A.QOKPRODPART,A.QOKARTST,A.QOKAUFKRS,A.QOKAUFLFD,A.QOKAUFPOS, "
                    + " TRIM(A.QOKART)||'-'||TRIM(A.QOKFARBE)||'-'||A.QOKVAR AS ARTICLE, A.QOKBESCHICH AS COLORDES , "
                    + " A.QOKAUFKRS||'-'||TO_NUMBER(A.QOKAUFLFD)||'-'||TO_NUMBER(A.QOKAUFPOS) AS VORDER,A.QOKKUNDPART,'' AS FACTORY, "
                    + " TO_NUMBER(A.QOKTP) AS TP,A.QOKVKAUFKRS||'-'||TO_NUMBER(A.QOKVKAUFLFD)||'-'||TO_NUMBER(A.QOKVKAUFPOS) AS KORDER, "
                    + " (SELECT B.QOEMWERT*0.001 FROM QOSTEP B  "
                    + " WHERE B.QOEMANDANT = A.QOKMANDANT AND B.QOEORDER = A.QOKORDER  "
                    + " AND B.QOEMERKM = 'JS  ') AS JS,(SELECT B.QOEMWERT*0.001 FROM QOSTEP B  "
                    + " WHERE B.QOEMANDANT = A.QOKMANDANT AND B.QOEORDER = A.QOKORDER  "
                    + " AND B.QOEMERKM = 'WS  ') AS WS,(SELECT B.QOEMWERT*0.001 FROM QOSTEP B  "
                    + " WHERE B.QOEMANDANT = A.QOKMANDANT AND B.QOEORDER = A.QOKORDER  "
                    + " AND B.QOEMERKM = 'XQMF') AS XQMF,(SELECT B.QOEMWERT*0.001 FROM QOSTEP B  "
                    + " WHERE B.QOEMANDANT = A.QOKMANDANT AND B.QOEORDER = A.QOKORDER  "
                    + " AND B.QOEMERKM = 'XHMF') AS XHMF,(SELECT B.QOEMWERT*0.001 FROM QOSTEP B  "
                    + " WHERE B.QOEMANDANT = A.QOKMANDANT AND B.QOEORDER = A.QOKORDER  "
                    + " AND B.QOEMERKM = 'QMQ ') AS QMQ,(SELECT B.QOEMWERT*0.001 FROM QOSTEP B  "
                    + " WHERE B.QOEMANDANT = A.QOKMANDANT AND B.QOEORDER = A.QOKORDER  "
                    + " AND B.QOEMERKM = 'GMC ') AS GMC,(SELECT B.QOEMWERT*0.001 FROM QOSTEP B  "
                    + " WHERE B.QOEMANDANT = A.QOKMANDANT AND B.QOEORDER = A.QOKORDER  "
                    + " AND B.QOEMERKM = 'SMC ') AS SMC,(SELECT B.QOEMWERT*0.001 FROM QOSTEP B  "
                    + " WHERE B.QOEMANDANT = A.QOKMANDANT AND B.QOEORDER = A.QOKORDER  "
                    + " AND B.QOEMERKM = 'KZ  ') AS KZ,(SELECT B.QOEMWERT*0.001 FROM QOSTEP B  "
                    + " WHERE B.QOEMANDANT = A.QOKMANDANT AND B.QOEORDER = A.QOKORDER  "
                    + " AND B.QOEMERKM = 'BJSP') AS BJSP,(SELECT B.QOEMWERT*0.001 FROM QOSTEP B  "
                    + " WHERE B.QOEMANDANT = A.QOKMANDANT AND B.QOEORDER = A.QOKORDER  "
                    + " AND B.QOEMERKM = 'AJSP') AS AJSP,(SELECT B.QOEMWERT*0.001 FROM QOSTEP B  "
                    + " WHERE B.QOEMANDANT = A.QOKMANDANT AND B.QOEORDER = A.QOKORDER  "
                    + " AND B.QOEMERKM = 'BWSP') AS BWSP,(SELECT B.QOEMWERT*0.001 FROM QOSTEP B  "
                    + " WHERE B.QOEMANDANT = A.QOKMANDANT AND B.QOEORDER = A.QOKORDER  "
                    + " AND B.QOEMERKM = 'AWSP') AS AWSP,(SELECT B.QOEMWERT*0.001 FROM QOSTEP B  "
                    + " WHERE B.QOEMANDANT = A.QOKMANDANT AND B.QOEORDER = A.QOKORDER  "
                    + " AND B.QOEMERKM = 'WX  ') AS WX,(SELECT B.QOEMWERT*0.001 FROM QOSTEP B  "
                    + " WHERE B.QOEMANDANT = A.QOKMANDANT AND B.QOEORDER = A.QOKORDER  "
                    + " AND B.QOEMERKM = 'XHWY') AS XHWY "
                    + " from QORDER A WHERE A.QOKMANDANT = '00' "
                    + " AND TRIM(A.QOKART)||'-'||TRIM(A.QOKFARBE)||'-'||A.QOKVAR LIKE '" + ARTICLE + "%'  "
                    + " AND  A.QOKPRODPART  LIKE '" + LOT + "%' "
                    + " AND  (A.QOKNEUD  >= '" + FROMDATE + "' or  '" + FROMDATE + "' is null) "
                    + " AND  (A.QOKNEUD <=  '" + ENDDATE + "'  or  '" + ENDDATE + "' is null) "
                    + " AND (A.QOKAUFKRS = :QOKAUFKRS OR :QOKAUFKRS IS NULL) "
                    + " AND (A.QOKAUFLFD = :QOKAUFLFD OR :QOKAUFLFD = '000000') "
                    + " AND (A.QOKAUFPOS = :QOKAUFPOS OR :QOKAUFPOS = '000') "
                    ;

                or1.Parameters.Add("QOKAUFKRS", OracleType.NVarChar).Value = dwABPOS["FKRS"].ToString();
                or1.Parameters.Add("QOKAUFLFD", OracleType.NVarChar).Value = dwABPOS["FLFD"].ToString();
                or1.Parameters.Add("QOKAUFPOS", OracleType.NVarChar).Value = dwABPOS["FPOS"].ToString();
                #endregion
                setLocal = Connect2Proxy(or1);
                if (setLocal.Tables.Count > 0)
                {
                    tableLocal = setLocal.Tables[0];
                    tableLocal.TableName = "QCORDER";
                    foreach (DataTable dtTemp in setLocal.Tables)
                    {

                        setResult = ImportDataTable(setResult, dtTemp);
                        DataTable tableABPOS = setResult.Tables["QCORDER"];
                        foreach (DataRow dwTmp in tableABPOS.Rows)
                        {
                            setResult = GetCOLORDES(setResult, dwTmp);
                            setResult = GetFACTORY(setResult, dwTmp);
                        }

                    }
                    /* 
                     byte[] b;
                     string value;
                     string ColumnName;
                     foreach (DataRow dwTemp in setLocal.Tables["QCORDER"].Rows)
                     {
                         ColumnName = "ARTICLE";
                         b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                         value = Encoding.GetEncoding("gb2312").GetString(b);
                         dwTemp[ColumnName] = value;

                         ColumnName = "COLORDES";
                         b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                         value = Encoding.GetEncoding("gb2312").GetString(b);
                         dwTemp[ColumnName] = value;

                         ColumnName = "SUPPLIER";
                         b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                         value = Encoding.GetEncoding("gb2312").GetString(b);
                         dwTemp[ColumnName] = value; 

                         ColumnName = "FACTORY";
                         b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                         value = Encoding.GetEncoding("gb2312").GetString(b);
                         dwTemp[ColumnName] = value;
                      
                       
                     }
                      */

                    set4Return.Merge(setResult);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return set4Return;
        }
        public DataSet GetCOLORDES(DataSet dsTarget, DataRow dwABPOS)
        {
            string TableName = "COLORDES";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT DISTINCT SUBSTR(SDDATEN,2,30) AS COLORDES FROM SDDAT "
                    + " WHERE SUBSTR(SDKEY,1,2)='00' AND trim(SUBSTR(SDKEY,3,10))=:ABPBESCHICH  AND SDSA = '9998' "
                     ;
                orlReadCommand.Parameters.Add("ABPBESCHICH", OracleType.NVarChar).Value = dwABPOS["COLORDES"].ToString().Trim();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;

                    byte[] b;
                    string value;
                    string ColumnName = "";
                    foreach (DataRow dw in dsResult.Tables["COLORDES"].Rows)
                    {
                        ColumnName = "COLORDES";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw[ColumnName] = value;

                        dwABPOS["COLORDES"] = dw["COLORDES"];
                    }
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    if (dsTarget.Tables.Contains(TableName))
                    {
                        dsTarget.Tables[TableName].Clear();
                    }
                    dsTarget = ImportDataTable(dsTarget, dtTemp);
                }
                dsTarget.AcceptChanges();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
        public DataSet GetFACTORY(DataSet dsTarget, DataRow dwABPOS)
        {
            string TableName = "FACTORY";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT TRIM(LSNAME1)AS FACTORY FROM ABPOS A,LIEFDAT B "
                    + " WHERE A.ABPMANDANT = B.LSMANDANT AND A.ABPLIEFNR = B.LSLIEFNR "
                    + " AND A.ABPAUFKRS = :AUFKRS AND A.ABPAUFLFD = :AUFLFD  "
                    + " AND A.ABPAUFPOS = :AUFPOS "
                     ;
                orlReadCommand.Parameters.Add("AUFKRS", OracleType.NVarChar).Value = dwABPOS["QOKAUFKRS"].ToString().Trim();
                orlReadCommand.Parameters.Add("AUFLFD", OracleType.NVarChar).Value = dwABPOS["QOKAUFLFD"].ToString().Trim();
                orlReadCommand.Parameters.Add("AUFPOS", OracleType.NVarChar).Value = dwABPOS["QOKAUFPOS"].ToString().Trim();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;

                    byte[] b;
                    string value;
                    string ColumnName = "";
                    foreach (DataRow dw in dsResult.Tables["FACTORY"].Rows)
                    {
                        ColumnName = "FACTORY";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw[ColumnName] = value;

                        dwABPOS["FACTORY"] = dw["FACTORY"];
                    }
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    if (dsTarget.Tables.Contains(TableName))
                    {
                        dsTarget.Tables[TableName].Clear();
                    }
                    dsTarget = ImportDataTable(dsTarget, dtTemp);
                }
                dsTarget.AcceptChanges();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
        public DataSet BuildGBBEW(DataRow dwUI)
        {
            string progress = "查询完成";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                EventDispatcher.Instance.RaiseStatus("查询开始", StatusEventType.StartProgress);
                setResult = GetRawData(dwUI);
                setReturn.Merge(setResult);
                EventDispatcher.Instance.RaiseStatus("查询完成", StatusEventType.StopProgress);
            }
            catch (Exception ex)
            {
                Tracer.Error(ABPOS + ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}